﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GU.WebERP.Model;
using System.Data;
using System.Collections;
using YGOP.ESB.Log;

namespace GU.WebERP.DAL
{
    public class DAL_Fct_Commodity : JnsFramework.Core.TableOperating
    {
        readonly JnsFramework.Core.ExecCustomizeSQL _exec = new JnsFramework.Core.ExecCustomizeSQL();
        readonly Model_Fct_Commodity _modelFct_Commodity = new Model_Fct_Commodity();

        public DAL_Fct_Commodity()
        {
            base.DBKey = "GU.ERPDb";
        }

        protected override JnsFramework.Common.IModel GetTableModel()
        {
            return _modelFct_Commodity;
        }

        /// <summary>
        /// 获取商品列表
        /// </summary>
        /// <param name="commodityName"></param>
        /// <param name="commodityCode"></param>
        /// <param name="state"></param>
        /// <param name="soldOut"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public DataSet GetList(string commodityName, string commodityCode, string state, string soldOut, int pageSize, int pageIndex, string sort)
        {
            StringBuilder sbwhere = new StringBuilder();
            sbwhere.Append(" and disabled=0");
            if (!string.IsNullOrWhiteSpace(commodityName))
            {
                sbwhere.Append(" and CommodityName like @CommodityName");
            }
            if (!string.IsNullOrWhiteSpace(commodityCode))
            {
                sbwhere.Append(" and CommodityCode = @CommodityCode");
            }
            if (!string.IsNullOrWhiteSpace(state))
            {
                sbwhere.Append(" and State = @State");
            }
            if (!string.IsNullOrWhiteSpace(soldOut))
            {
                sbwhere.Append(" and IsSoldOut = @IsSoldOut");
            }

            StringBuilder sbsql = new StringBuilder();
            sbsql.Append(" select * from");
            sbsql.Append(" (");
            sbsql.Append("SELECT");
            sbsql.Append(" ROW_NUMBER() over(order by @Sort) 'pagenum'");
            sbsql.Append(" ,[CommodityID],[CommodityName],[Spec],[Stock],[CommodityCode],[State],[Weight],[Description],[Durability],[DeliveryTime],[OriginalPrice],[CurrentPrice] ");
            sbsql.Append(" ,[IsSoldOut],[ListPicture],[DetailPicture],[Disabled],[Rec_CreateBy],[Rec_CreateTime],[Rec_ModifyBy],[Rec_ModifyTime]");
            sbsql.Append(" FROM [GU.ERP].[dbo].[Fct_Commodity] where 1=1");
            sbsql.Append(sbwhere);
            sbsql.Append(" ) t");
            sbsql.Append(" where pagenum > " + (pageIndex) * pageSize + " and pagenum<=" + (pageIndex + 1) * pageSize + " ");

            StringBuilder sbCount = new StringBuilder();
            sbCount.Append("select count(1) from Fct_Commodity where 1=1");
            sbCount.Append(sbwhere);

            try
            {
                Hashtable hs = new Hashtable();
                hs.Add("@CommodityName", commodityName);
                hs.Add("@CommodityCode", commodityCode);
                hs.Add("@State", state);
                hs.Add("@IsSoldOut", soldOut);
                hs.Add("@Sort", sort == null ? "Fct_Commodity.Rec_ModifyTime" : soldOut);

                DataTable dt = _exec.Query(sbsql.ToString(), hs);
                DataTable count = _exec.Query(sbCount.ToString(), hs);

                DataSet ds = new DataSet();
                ds.Tables.Add(count);
                ds.Tables.Add(dt.Copy());

                return ds;
            }
            catch (Exception ex)
            {
                LogManager.WriteDbError("获取商品列表", sbsql.ToString(), "", ex);
            }
            return null;
        }
    }
}
